package com.xpgk.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import com.jfinal.plugin.activerecord.Record;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtil {
	public static void createExcel(OutputStream os, List<Record> list, String[] headers, String[] columns)
			throws WriteException, IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);

		WritableSheet sheet = workbook.createSheet("Sheet1", 0);
		
		String[] headerss={"日期","新增","姓名","首次到店日期","单疗程","单疗程","单疗程","标准疗程","标准疗程","标准疗程","标准疗程","转疗程","转疗程","转疗程","减重金额合计","按摩膏","按摩膏","按摩精油","按摩精油","产品金额合计","总计(减重+产品)"};
		String[] headersss={"","","","","人数","单价","金额","人数","疗程数","单价","金额","人数","疗程数","金额","","数量","金额","数量","金额","",""};
	    String[] columnss={"date","newadd","name","intotime","singlelc_peoplenum",
	    		"singlelc_unitprice","singlelc_totalprice","normlc_peoplenum",
	    		"normlc_lcnum","normlc_unitprice","normlc_totalprice","uplc_peoplenum","uplc_lcnum","","","ss002_num","ss002_price","uplc_totalprice","lctotalprice"};

		int row = 0;

		for (int i = 0; i < headerss.length; i++) {
			String header = headerss[i];
			WritableCellFormat format = new WritableCellFormat();
			format.setBackground(Colour.GRAY_25);//背景色
			format.setAlignment(Alignment.CENTRE);//水平对齐
			format.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
			format.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
			format.setWrap(true);//是否换行
			sheet.addCell(new Label(i, row, header, format));
		}
		sheet.mergeCells(0, 0, 0, 1);
		sheet.mergeCells(1, 0, 1, 1);
		sheet.mergeCells(2, 0, 2, 1);
		sheet.mergeCells(3, 0, 3, 1);
		sheet.mergeCells(4, 0, 6, 0);
		sheet.mergeCells(7, 0, 10, 0);
		sheet.mergeCells(11, 0, 13, 0);
		sheet.mergeCells(14, 0, 14, 1);
		sheet.mergeCells(15, 0, 16, 0);
		sheet.mergeCells(17, 0, 18, 0);
		sheet.mergeCells(19, 0, 19, 1);
		sheet.mergeCells(20, 0, 20, 1);
		
		row++;
		
		for (int i = 0; i < headersss.length; i++) {
			String header = headersss[i];
			WritableCellFormat format = new WritableCellFormat();
			format.setBackground(Colour.GRAY_25);
			format.setAlignment(Alignment.CENTRE);
			format.setVerticalAlignment(VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN);
			format.setWrap(true); 
			sheet.addCell(new Label(i, row, header, format));
		}
		
		row++;

		for (int j =0; j < list.size(); j++) {
			Record record = (Record) list.get(j);

			String[] values = getValues(columnss, record);
			for (int i = 0; i < values.length; i++) {
				sheet.addCell(new Label(i, row, values[i]));
			}
			row++;
		}

		workbook.write();
		workbook.close();
	}

	private static String[] getValues(String[] columns, Record record) {
		String[] values = new String[columns.length];
		int i = -1;
		for (String col : columns) {
			i++;
			Object value = record.get(col);
			if (value == null) {
				continue;
			}
			values[i] = value.toString();
		}
		return values;
	}

}
